﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using SQX.DataModel;
using System.Data;

namespace SQX.ViewModel
{
    public class SQLViewModel : QueryViewModelBasic
    {
        private string connectionString = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Persist Security Info=True;";
        private SqlConnection con;
        private string server;
        private string database;
        private List<SqlServer> servers = new List<SqlServer>();
        private string exmessage = "";


        public SQLViewModel(string serverName, string databaseName)
        {
            this.server = serverName;
            this.database = databaseName;
            con = new SqlConnection(string.Format(this.connectionString, this.server, this.database));
            base.Markup = new SyntaxMarkup(Service.SourceType.SourceSQL);
            base.modelName = "SQL set, operator: " + Environment.UserDomainName + @"\" + Environment.UserName;
        }

        public Task InitializeModelAsync()
        {
            Task t = new Task(InitalizeModel);
            t.Start();
            return t;
        }

        private void InitalizeModel()
        {
            try
            {
                con.Open();
                var srv = new SqlServer(this.server);
                this.servers.Add(srv);
                var db = new SqlDatabase(con.Database);
                srv.Databases.Add(db);

                SqlDatabaseNode tableNode = new SqlDatabaseNode("Tables");
                SqlDatabaseNode viewNode = new SqlDatabaseNode("Views");
                SqlDatabaseNode programNode = new SqlDatabaseNode("Programmability");

                db.Nodes.Add(viewNode);
                db.Nodes.Add(tableNode);
                db.Nodes.Add(programNode);


                DataTable t = con.GetSchema("Tables");
                DataTable columns = con.GetSchema("Columns");
                DataTable pt = con.GetSchema("Procedures");

                t.Rows.Cast<DataRow>().ToList().ForEach(rw =>
                    {
                        if (rw[3].ToString().ToLower().Contains("view"))
                        {
                            var vt = new SqlView(rw[2].ToString(), rw[1].ToString());
                            columns.Rows.Cast<DataRow>().ToList().FindAll(vtrw =>
                                {
                                    return vtrw[2].ToString() == vt.ObjectName;
                                }).ForEach(vt_found =>
                                    {
                                        var vt_column = new SqlColumn(vt_found[3].ToString(), vt_found[7].ToString());
                                        vt.Columns.Add(vt_column);
                                    });
                            var vobj = new DatabaseElement(vt.ObjectName, "View");
                            QueryHelpList.Add(vobj);
                            viewNode.SqlMembers.Add(vt);
                        }
                        else
                        {
                            var bt = new SqlTable(rw[2].ToString(), rw[1].ToString());
                            columns.Rows.Cast<DataRow>().ToList().FindAll(btrw =>
                            {
                                return btrw[2].ToString() == bt.ObjectName;
                            }).ForEach(bt_found =>
                            {
                                var bt_column = new SqlColumn(bt_found[3].ToString(), bt_found[7].ToString());
                                bt.Columns.Add(bt_column);
                            });
                            var bobj = new DatabaseElement(bt.ObjectName, "Table");
                            QueryHelpList.Add(bobj);
                            tableNode.SqlMembers.Add(bt);
                        }
                    });
                
                pt.Rows.Cast<DataRow>().ToList().ForEach(rw =>
                    {
                        if (rw[6].ToString().ToLower().Contains("function"))
                        {
                            var ft = new SqlScalarFunction(rw[2].ToString(), rw[1].ToString());
                            var fobj = new DatabaseElement(ft.ObjectName, "Function");
                            QueryHelpList.Add(fobj);
                            programNode.SqlMembers.Add(ft);
                        }
                        else if (rw[6].ToString().ToLower().Contains("procedure"))
                        {
                            var proct = new SqlProcedure(rw[2].ToString(), rw[1].ToString());
                            var procobj = new DatabaseElement(proct.ObjectName, "Stored procedure");
                            var cmd = new SqlCommand(proct.ObjectCode, con);
                            cmd.CommandType = CommandType.StoredProcedure;
                            SqlCommandBuilder.DeriveParameters(cmd);
                            cmd.Parameters.Cast<SqlParameter>().ToList().ForEach(spp =>
                                {
                                    var spp_column = new SqlColumn(spp.ParameterName, spp.SqlDbType.ToString());
                                    proct.Columns.Add(spp_column);
                                });
                            QueryHelpList.Add(procobj);
                            programNode.SqlMembers.Add(proct);
                        }
                    });


                Markup.LanguageObjects.ForEach(obj =>
                    {
                        QueryHelpList.Add(obj);
                    });

                var query = new SqlQuery("Query " + Guid.NewGuid().ToString(), "", con);
                base.activeQuery = query;
                base.QueryCollection.Add(query);
            }
            catch(Exception ex)
            {
                exmessage = ex.Message;
            }
        }

        public List<SqlServer> Servers { get { return servers; } }

        public string ExceptionMessage { get { return exmessage; } }

    }

    public class SqlServer : SqxObject
    {
        private List<SqlDatabase> databases;

        public SqlServer(string ServerName) : base(ServerName)
        {
            databases = new List<SqlDatabase>();
        }

        public List<SqlDatabase> Databases { get { return databases; } }
    }

    public class SqlDatabase : SqxObject
    {
        private List<SqlDatabaseNode> nodes;
        
        public SqlDatabase(string DatabaseName) : base(DatabaseName)
        {
            nodes = new List<SqlDatabaseNode>();
        }

        public List<SqlDatabaseNode> Nodes { get { return nodes; } }
       
    }

    public class SqlDatabaseNode : SqxObject
    {
        private List<SqxObject> members;

        public SqlDatabaseNode(string NodeName) : base(NodeName)
        {
            members = new List<SqxObject>();
        }

        public List<SqxObject> SqlMembers { get { return members; } }
    }

    public class SqlColumn : SqxObject
    {
        public SqlColumn(string Name, string TypeDesc) : base(Name)
        {
            this.ObjectCode = TypeDesc;
        }
    }

    public class SqlTable : SqxObject
    {
        private List<SqlColumn> columns;

        public SqlTable(string TableName, string SchemaName) : base(TableName)
        {
            columns = new List<SqlColumn>();
            this.ObjectCode = SchemaName + "." + TableName;
        }

        public List<SqlColumn> Columns { get { return columns; } }
    }

    public class SqlView : SqxObject
    {
        private List<SqlColumn> columns;

        public SqlView(string ViewName, string SchemaName) : base(ViewName)
        {
            columns = new List<SqlColumn>();
            this.ObjectCode = SchemaName + "." + ViewName;
        }

        public List<SqlColumn> Columns { get { return columns; } }
    }

    public class SqlProcedure : SqxObject
    {
        private List<SqlColumn> columns;

        public SqlProcedure(string ProcedureName, string SchemaName) : base(ProcedureName)
        {
            columns = new List<SqlColumn>();
            this.ObjectCode = SchemaName + "." + ProcedureName;
        }

        public List<SqlColumn> Columns { get { return columns; } }
    }

    public class SqlScalarFunction : SqxObject
    {
        public SqlScalarFunction(string FunctionName, string SchemaName) : base(FunctionName)
        {
            this.ObjectCode = SchemaName + "." + FunctionName;
        }
    }

    public class SqlTableFunctions : SqxObject
    {
        public SqlTableFunctions(string FunctionName) : base(FunctionName)
        {

        }
    }

    public abstract class SqxObject
    {
        private string objectName;
       

        public SqxObject(string Name)
        {
            this.objectName = Name;
        }

        public string ObjectName { get { return objectName; } }
        public string ObjectCode { get; set; }
    }

}
